﻿Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.IO
Imports System.Text


Partial Public Class export
    Inherits System.Web.UI.Page
    Dim db As New hc_databaseDataContext
    Dim Kennzahlen As New List(Of String)
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim settings As ConnectionStringSettings
        settings = System.Configuration.ConfigurationManager.ConnectionStrings("PMSConnectionString")
        Dim connectionString As String = settings.ConnectionString


        Dim i As Integer
        Dim strLine As String, filePath, fileName, fileExcel, link
        Dim objFileStream As FileStream
        Dim objStreamWriter As StreamWriter
        Dim nRandom As Random = New Random(DateTime.Now.Millisecond)
        Dim fs As Object, myFile As Object
        Dim cnn As SqlConnection = New SqlConnection(connectionString)

        'Create a pseudo-random file name.
        fileExcel = "controling10.xls"

        'Set a virtual folder to save the file.
        'Make sure that you change the application name to match your folder.
        filePath = Server.MapPath("\ExcelVBTest")
        fileName = filePath & "\" & fileExcel

        'Use FileStream to create the .xls file.

        objFileStream = New FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write)
        objStreamWriter = New StreamWriter(objFileStream)



        'Write the field name information to file.
        '  objStreamWriter.WriteLine("Hallt" & Chr(9) & "die")
        objStreamWriter.WriteLine(ersteZeileZusammensetzen)
        objStreamWriter.WriteLine(zweiteZeileZusammensetzen)




        cnn.Close()
        objStreamWriter.Close()
        objFileStream.Close()

        'Show a link to the Excel file.
        HyperLink1.Text = "Open Excel"
        HyperLink1.NavigateUrl = "/excelvbtest/controling10.xls"

    End Sub
    Private Function kennzahlenBerechnen() As List(Of String)


        '  Anzahl der Mitglieder, die innerhalb der letzten 3 Monate mind. 12 PT auf externen Projekten aktiv waren
        Dim anteilMitglieder12PT_1 = (From mg In db.Mitgliede_Anwaerter, rp In db.Reports, pr In db.Projekts, aa In db.Arbeitet_ans _
                                           Where mg.ID = rp.Mitgliede_AnwaerterID And mg.ID = aa.Mitgliede_AnwaerterID And aa.ProjektID = pr.ID _
                                           And rp.Datum > DateAdd("d", -90, Now) And rp.Angenommen_pl = 1 And pr.Projekt_extern_intern = 0 _
                                        Group By mg.ID Into g = Sum(rp.Dauer) Where g > 96 _
                                        Select g).Count

        Kennzahlen.Add("Anzahl der Mitglieder, die innerhalb der letzten 3 Monate mind. 12 Pt auf externen Projekten aktiv waren")
        Kennzahlen.Add(anteilMitglieder12PT_1.ToString)

        'Anzahl der PMs, die innherhalb der letzten 6 Monate mindestens 4 Projekte betreut haben
        'Dim anzahlPmsProjekt_4 = From mg In db.Mitgliede_Anwaerter, aa In db.Arbeitet_ans, pr In db.Projekts _
        '                                         Where pr.Startdatum_erstkontakt > DateAdd("d", -180, Now) And mg.ID = aa.Mitgliede_AnwaerterID _
        '                                         And aa.ProjektID = pr.ID And pr.Projekt_extern_intern = 0 And aa.rolle = "PM" Or aa.rolle = "JPM" And pr.status IsNot "Angebot-Abgelehnt" _
        '                                        Group aa By aa.Mitgliede_AnwaerterID Into Group Where Group.Count > 4 _
        '                                        Select Group, bla = Group.Count

        Dim alleMitglieder = (From mg In db.Mitgliede_Anwaerter Select mg.ID).ToList

        Dim allepms As New List(Of Integer)
        For i = 0 To alleMitglieder.Count - 1
            Dim bla = i
            '   Dim wievielePmprojekte = (From pr In db.Projekts, aa In db.Arbeitet_ans _
            ' Where pr.Startdatum_erstkontakt > DateAdd("d", -180, Now) And alleMitglieder(bla) = aa.Mitgliede_AnwaerterID _
            ' And aa.ProjektID = pr.ID And pr.Projekt_extern_intern = 0 And aa.rolle = "PM" Or aa.rolle = "JPM" _
            ' And pr.status <> "Angebot-Abgelehnt" _
            'Distinct Select pr.ID).ToList

            Dim wievielepmprojekte = (From aa In db.Arbeitet_ans _
                                     Where aa.Mitgliede_AnwaerterID = alleMitglieder(bla) _
                                     Select aa).Distinct.ToList

            Dim blall = (From it In wievielepmprojekte _
                      Where it.Projekt.Startdatum_erstkontakt > DateAdd(DateInterval.Day, -180, Now) And _
                    it.rolle = "PM" Or it.rolle = "Jpm" And it.Projekt.status <> "Angebot-Abgelehnt" And it.Projekt.Projekt_extern_intern = 0 _
                    Select it).Count

            If blall > 3 Then
                allepms.Add(alleMitglieder(i))
            End If

        Next
        Dim anzahlPmsProjekt_4 = allepms.Count

        Kennzahlen.Add("Anzahl der PMs, die innherhalb der letzten 6 Monate mindestens 4 Projekte betreut haben")
        Kennzahlen.Add(anzahlPmsProjekt_4.ToString)




        'Dauer von Interessent zu Anwärter ist <= X ' hier fehlt noch übergang zur GAST
        Dim dauerinteressenentAnwaerter_51 = ((From mg In db.Mitgliede_Anwaerter _
        Where mg.ErsterHcAbend.HasValue And mg.ZielgespraechAP.HasValue _
    Select DateDiff(DateInterval.Day, CDate(mg.ErsterHcAbend), CDate(mg.ZielgespraechAP))).ToList).Average

        Kennzahlen.Add("Dauer von Interessent zu Anwärter ist <= X")
        Kennzahlen.Add(dauerinteressenentAnwaerter_51.ToString)


        'Anwärter zu MG <= X
        Dim daueranwaerterMitglied_52 = ((From mg In db.Mitgliede_Anwaerter _
                                                  Where mg.ZielgespraechAP.HasValue And mg.Mitgliedsausfnahmegespraech.HasValue _
        Select DateDiff(DateInterval.Day, mg.ZielgespraechAP.Value, mg.Mitgliedsausfnahmegespraech.Value)).ToList).Average

        Kennzahlen.Add("Anwärter zu MG <= X")
        Kennzahlen.Add(daueranwaerterMitglied_52.ToString)

        'MG zu JPM <= X
        Dim daueramitgliedJpm53 = ((From mg In db.Mitgliede_Anwaerter _
                      Where mg.Mitgliedsausfnahmegespraech.HasValue And mg.ZielgespraechJPM.HasValue _
               Select DateDiff(DateInterval.Day, mg.Mitgliedsausfnahmegespraech.Value, mg.ZielgespraechJPM.Value)).ToList).Average

        Kennzahlen.Add("MG zu JPM <= X")
        Kennzahlen.Add(daueramitgliedJpm53.ToString)


        ' JPM Projektabschluss zu PM <= X.
        Dim dauerjpmPM54 = ((From mg In db.Mitgliede_Anwaerter _
                Where mg.ZielgespraechJPM.HasValue And mg.PmAufnahmeGespraech.HasValue _
                       Select DateDiff(DateInterval.Day, mg.ZielgespraechJPM.Value, mg.PmAufnahmeGespraech.Value)).ToList).Average

        Kennzahlen.Add("JPM Projektabschluss zu PM <= X.")
        Kennzahlen.Add(dauerjpmPM54.ToString)


        'Dauer bis zum ersten externen Projekt 

        Dim mitgliede_cache = (From it In db.Mitgliede_Anwaerter _
                                    Where it.ErsterHcAbend.HasValue _
                                    Select it.ID).ToList

        Dim ergebnis As New List(Of Integer)
        For i = 0 To mitgliede_cache.Count - 1

            Dim var = (From mg In db.Mitgliede_Anwaerter, pr In db.Projekts, aa In db.Arbeitet_ans _
                      Where mg.ID = aa.Mitgliede_AnwaerterID And aa.ProjektID = pr.ID And mg.ID = mitgliede_cache(i) _
                        Select pr.ID).ToList
            Dim Var3 As New List(Of Integer)

            For p = 0 To var.Count - 1

                Dim var2 = (From pr In db.Projekts, mg In db.Mitgliede_Anwaerter _
                            Where mg.ID = mitgliede_cache(i) And pr.ID = var(p) And pr.Startdatum_erstkontakt.HasValue _
                          Select Datum = DateDiff(DateInterval.Day, mg.ErsterHcAbend.Value, pr.Startdatum_erstkontakt.Value).ToString).Single

                Var3.Add(Integer.Parse(var2))

            Next
            If Var3.Count > 0 Then
                ergebnis.Add(Var3.Min())
            End If
        Next

        Dim dauerbisExternProje_63 As Integer = ergebnis.Average

        Kennzahlen.Add("Dauer bis zum ersten externen Projekt")
        Kennzahlen.Add(dauerbisExternProje_63.ToString)


        'Anzahl Projektanfragen pro Monat
        Dim anzahlProjektanfragen_91 = ((From pr In db.Projekts _
                                       Where pr.Startdatum_erstkontakt > DateAdd("d", -365, Now) _
                                       And pr.Projekt_extern_intern <> 1 _
                                       Select pr).Count / 12)

        Kennzahlen.Add("Anzahl Projektanfragen pro Monat")
        Kennzahlen.Add(anzahlProjektanfragen_91.ToString)

        ' X % aller Akquise/Marketing-Maßnahmen erreichen Wirkungsgrad (Projektanfragen pro PT Maßnahme) > 1
        'X % alle Akquise/Marketing-Maßnahmen erreichen Wirkungsgrad (zustandegekommene Projekte pro PT Maßnahme) > 0,75
        'Stammkundenprojekte /Gesamtprojekte > x (Betrachtungszeitraum: immer die letzten 12 Monate)
        'erst mal alle Stammkunden raus filtern
        Dim stammkunde = From ku In db.Kundes, pr In db.Projekts _
                          Where pr.status <> "Angebot-Abgelehnt" And pr.Startdatum_erstkontakt > DateAdd("d", -730, Now) _
                          And pr.KundeID = ku.ID _
                          Group By ku Into g = Count(pr.ID) Where g > 1 _
                          Select ku

        Dim gesamtprojekte = From pr In db.Projekts _
                          Where pr.status <> "Angebot-Abgelehnt" And pr.Startdatum_erstkontakt > DateAdd("d", -730, Now) _
                          Select pr

        Dim AnzahlStammkundenProjekt_112 = (stammkunde.Count / gesamtprojekte.Count)

        Kennzahlen.Add("Stammkundenprojekte /Gesamtprojekte > x (Betrachtungszeitraum: immer die letzten 24 Monate)")

        Kennzahlen.Add(AnzahlStammkundenProjekt_112.ToString)



        'X% aller HC'ler müssen alle 3 Monate 12 PT intern leisten; Zahlen aus ROI (Anzahl PT pro Woche
        'pro HC-ler, nicht Durchschnitt); "

        Dim anzahlHcler12PTIntern_133 = (From mg In db.Mitgliede_Anwaerter, rp In db.Reports, pr In db.Projekts, aa In db.Arbeitet_ans _
                                           Where mg.ID = rp.Mitgliede_AnwaerterID And mg.ID = aa.Mitgliede_AnwaerterID And aa.ProjektID = pr.ID _
                                           And pr.Startdatum_erstkontakt > DateAdd("d", -90, Now) And rp.Angenommen_pl = 1 And pr.Projekt_extern_intern = 1 _
                                        Group By mg.ID Into g = Sum(rp.Dauer) Where g > 96 _
                                        Select g).Count

        Kennzahlen.Add("X% aller HC'ler müssen alle 3 Monate 12 PT intern leisten")

        Kennzahlen.Add(anzahlHcler12PTIntern_133.ToString)

        ' Anzahl der Projekte mit unterschiedlichen Themenbereichen (Betrachtungszeitraum: immer die letzten 12 Monate)
        Dim AnzahlverschSchema_142 = (From pr In db.Projekts _
                             Where pr.Startdatum_erstkontakt > DateAdd("d", -356, Now) And pr.Projekt_extern_intern = 0 _
                             And pr.status <> "Angebot-Abgelehnt" Group By pr.Projektschema Into Group).count

        Kennzahlen.Add("Anzahl der Projekte mit unterschiedlichen Themenbereichen (Betrachtungszeitraum: immer die letzten 12 Monate")

        Kennzahlen.Add(AnzahlverschSchema_142.ToString)

        'Anzahl der Projekte in unterschiedlichen Branchen  (Betrachtungszeitraum: immer die letzten 12 Monate)
        Dim AnzahlProjektBranchen_143 = (From pr In db.Projekts, ku In db.Kundes _
                                                         Where pr.Startdatum_erstkontakt > DateAdd("d", -356, Now) And pr.KundeID = ku.ID _
                                                         And ku.Name <> "Hanseatic Consulting" And pr.status <> "Angebot-Abgelehnt" _
                                                         Group By ku.Branche Into Group).Count

        Kennzahlen.Add("Anzahl der Projekte in unterschiedlichen Branchen  (Betrachtungszeitraum: immer die letzten 12 Monate)")

        Kennzahlen.Add(AnzahlProjektBranchen_143.ToString)



        'Anteil der Mtiglieder die mehr als 15 PT pro Jahr leisten
        Dim anteilMitglieder15Pt151 = (From mg In db.Mitgliede_Anwaerter, rp In db.Reports, pr In db.Projekts, aa In db.Arbeitet_ans _
                                      Where mg.ID = rp.Mitgliede_AnwaerterID And mg.ID = aa.Mitgliede_AnwaerterID And aa.ProjektID = pr.ID _
                                      And pr.Startdatum_erstkontakt > DateAdd("d", -356, Now) And rp.Angenommen_pl = 1 _
                                   Group By mg.ID Into g = Sum(rp.Dauer) Where g > 120 _
                                   Select g).Count

        Kennzahlen.Add("Anteil der Mtiglieder die mehr als 15 PT pro Jahr leisten")

        Kennzahlen.Add(anteilMitglieder15Pt151.ToString)


        ' Anteil der Projektaktiven Mitglieder im letzten JAhr
        Dim projektaktiveMitglieder_152 = ((From mg In db.Mitgliede_Anwaerter, aa In db.Arbeitet_ans, pr In db.Projekts _
                                         Where pr.Startdatum_erstkontakt > DateAdd("d", -356, Now) And mg.ID = aa.Mitgliede_AnwaerterID _
 _
                                         And aa.ProjektID = pr.ID And pr.Projekt_extern_intern = 0 And pr.status <> "Angebot-Abgelehnt" _
                                        Distinct Select mg.ID).Distinct).Count

        Kennzahlen.Add("Anteil der Projektaktiven Mitglieder im letzten JAhr")
        Kennzahlen.Add(projektaktiveMitglieder_152.ToString)


        Return Kennzahlen
    End Function

    Private Function ersteZeileZusammensetzen()
        Dim bla = kennzahlenBerechnen()
        Dim zeilenstring As String = ""

        For i = 0 To bla.Count - 1 Step 2
            zeilenstring = zeilenstring + Chr(9) + bla(i)

        Next i

        Return zeilenstring
    End Function

    Private Function zweiteZeileZusammensetzen()
        Dim bla = Kennzahlen
        Dim zeilenstring As String = ""

        For i = 1 To bla.Count - 1 Step 2
            zeilenstring = zeilenstring + Chr(9) + bla(i)

        Next i

        Return zeilenstring
    End Function
End Class